ALTER PROCEDURE [dbo].[ReportTelStatistics]
@deptNo VARCHAR(50)
AS
DECLARE @resultSet TABLE(
    deptName VARCHAR(50),
    subNotSignTelNum int DEFAULT 0,
    subNotReplyTelNum int DEFAULT 0,
    subBackTelNum int DEFAULT 0,
    subBlueTelNum int DEFAULT 0
)
DECLARE @deptName VARCHAR(50)
DECLARE @notSignCount int
DECLARE @noReplyCount int
DECLARE @sendBackCount int
DECLARE @overTimeCount int
DECLARE @subDeptNo VARCHAR(50)
DECLARE @subDeptName VARCHAR(50)
BEGIN
    If LEN(@deptNo)>3 AND LEFT(@deptNo,3)='304' SET @deptNo=STUFF(@deptNo,1,3,'301')
    DECLARE subDeptSet CURSOR FOR
        SELECT NoUnit subDeptNo,DeptName subDeptName
        FROM bUserDept
        WHERE isDel = 0 AND useState = 1 AND NoParent = @deptNo
        ORDER BY ordNum
    OPEN subDeptSet
        FETCH FROM subDeptSet INTO @subDeptNo,@subDeptName
        WHILE (@@FETCH_STATUS = 0)
            BEGIN
                SELECT @deptName=@subDeptName
                SELECT @notSignCount = SUM(CASE WHEN SendState IN (10,11) THEN 1 ELSE 0 END),
                       @noReplyCount = SUM(CASE WHEN SendState IN (21,23) AND BackState IN (0,12,22) then 1 ELSE 0 END),
                       @sendBackCount = SUM(CASE WHEN SendState = 40 THEN 1 ELSE 0 END),
                       @overTimeCount = SUM(CASE WHEN SendState IN(21,23) AND BackState IN (0,12,22) AND DATEDIFF(DAY, GETDATE(), BackLimit)<0 THEN 1 ELSE 0 END)+SUM(CASE WHEN SendState IN (10,11) AND DATEDIFF(DAY, GETDATE(), BackLimit)<0 THEN 1 ELSE 0 END)
                FROM tWfSendBack WHERE IsDel= 0 AND dbo.frTelWFStep(WfId)>399 AND SBDeptID=@subDeptNo
                    if @notSignCount IS NULL SET @notSignCount=0
                    if @noReplyCount IS NULL SET @noReplyCount=0
                    if @sendBackCount IS NULL SET @sendBackCount=0
                    if @overTimeCount IS NULL SET @overTimeCount=0
                INSERT INTO @resultSet(deptName,subNotSignTelNum,subNotReplyTelNum,subBackTelNum,subBlueTelNum) VALUES(@deptName,@notSignCount,@noReplyCount,@sendBackCount,@overTimeCount)
            FETCH NEXT FROM subDeptSet INTO @subDeptNo,@subDeptName
            END
    CLOSE subDeptSet
    DEALLOCATE subDeptSet
SELECT DISTINCT * FROM @resultSet
END